/**
 * @Author: 17197
 * @Date: 2024-05-01 14:32:40
 * @Description: articleModel.js
 * @Version: 1.0
 * @Last Modified time : 2024-05-01 14:32:40
 **/
const {getDatabase} = require('../../config/db');

// 获取文章列表
const getArticleList = async (page, limit, title, status, admin, sort) => {
	try {
		let sql = `SELECT
                a.*,
                u.username,
                u.avatar
            FROM
                articles a
            JOIN
                users u ON a.user_id = u.id
            WHERE
                1 = 1
        `;
		let countSql = `
            SELECT
				COUNT(DISTINCT a.id) as total
            FROM
                articles a
            JOIN
                users u ON a.user_id = u.id
            WHERE
                1 = 1
        `;
		let params = [];
		let countParams = [];
		if (title) {
			sql += ` AND a.title LIKE ?`;
			countSql += ` AND a.title LIKE ?`;
			params.push(`%${title}%`);
			countParams.push(`%${title}%`);
		}
		if (!admin) {
			sql += ' AND status = 2';
			countSql += ' AND status = 2';
		} else {
			if (status) {
				sql += ' AND status = ?';
				countSql += ' AND status = ?';
				params.push(status);
				countParams.push(status);
			}
		}
		if (sort === '2') {
			sql += ' ORDER BY a.likes DESC';
		} else {
			sql += ' ORDER BY a.create_time DESC';
		}
		sql += ` LIMIT ${(page - 1) * limit}, ${limit}`;

		const connection = getDatabase();
		const [rows] = await connection.execute(sql, params);
		const [count] = await connection.execute(countSql, countParams);
		return {
			Rows: rows,
			total: count[0].total
		};
	} catch (error) {
		console.error(error);
		return null;
	}
}

// 获取文章详情
const getArticleDetail = async (id) => {
	try {
		const sql = `
			SELECT
				a.*,
				u.username,
				u.avatar
			FROM
				articles a
			JOIN
				users u ON a.user_id = u.id
			WHERE
				a.id = ?
		`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, [id]);
		return rows[0];
	} catch (error) {
		console.error(error);
		return null;
	}
}

// 发布文章
const publishArticle = async (data) => {
	try {
		const sql = `INSERT INTO articles (user_id, title, description, content, tags, create_time) VALUES (?, ?, ?, ?, ?, ?)`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, [data.user_id, data.title, data.description, data.content, data.tags, new Date()]);
		return rows.affectedRows === 1;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 删除文章
const deleteArticle = async (id) => {
	try {
		const sql = `DELETE FROM articles WHERE id = ?`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, [id]);
		return rows.affectedRows === 1;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 获取必读榜（点赞量最多的前10篇文章）
const getReadList = async () => {
	try {
		const sql = `
		SELECT
                a.*,
                u.username,
                u.avatar
            FROM
                articles a
            JOIN
                users u ON a.user_id = u.id
            WHERE
                1 = 1
 				ORDER BY likes DESC LIMIT 10`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql);
		return rows;
	} catch (error) {
		console.error(error);
		return null;
	}
}

// 获取文章评论
const getArticleComment = async (articleId, page, limit, sort) => {
	try {
		// 返回total（只查询没有parent_comment_id的总条数）,page,limit,rows
		let sql = `
			SELECT
				ac.*,
				u.username,
				u.avatar,
				(
					SELECT
						COUNT(*)
					FROM
						article_comment_likes acl
					WHERE
						acl.comment_id = ac.id
				) AS like_accounts
			FROM
				article_comments ac
			JOIN
				users u ON ac.user_id = u.id
			WHERE
				ac.article_id = ?
		`;
		let countSql = `
			SELECT
				COUNT(*) AS total
			FROM
				article_comments
			WHERE
				article_id = ?
			AND
				parent_comment_id IS NULL
		`;
		let params = [articleId];
		let countParams = [articleId];
		if (sort === '1') {
			console.log('最新排序')
			sql += ' ORDER BY ac.comment_time DESC';
		} else if (sort === '2') {
			console.log('最热排序')
			sql += ' ORDER BY like_accounts DESC';
		}
		sql += ` LIMIT ${(page - 1) * limit}, ${limit}`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, params);
		const [count] = await connection.execute(countSql, countParams);
		return {
			total: count[0].total,
			page,
			limit,
			rows
		};
	} catch (error) {
		console.error(error);
		return null;
	}

}

// 查询是否评论点赞
const queryIsLike = async (commentId, userId) => {
	try {
		const sql = `
			SELECT
				*
			FROM
				article_comment_likes
			WHERE
				comment_id = ?
			AND
				user_id = ?
		`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, [commentId, userId]);
		return rows.length > 0;
	} catch (error) {
		console.error(error);
		return null;
	}
}

// 评论点赞
const commentLike = async (commentId, userId) => {
	try {
		// 这里应该先查询是否已经点赞，如果已经点赞则取消点赞（删除记录），如果没有点赞则点赞（插入记录），同时更新评论表的点赞数
		const isLike = await queryIsLike(commentId, userId);
		const connection = getDatabase();
		if (isLike) {
			const sql = `DELETE FROM article_comment_likes WHERE comment_id = ? AND user_id = ?`;
			await connection.execute(sql, [commentId, userId]);
		} else {
			const sql = `INSERT INTO article_comment_likes (comment_id, user_id) VALUES (?, ?)`;
			await connection.execute(sql, [commentId, userId]);
		}
		const updateSql = `UPDATE article_comments SET like_accounts = (SELECT COUNT(*) FROM article_comment_likes WHERE comment_id = ?) WHERE id = ?`;
		await connection.execute(updateSql, [commentId, commentId]);
		return true;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 发表评论
const publishComment = async (data, userId) => {
	try {
		// parent_comment_id可能为空
		const sql = `INSERT INTO article_comments (article_id, user_id, parent_comment_id, content) VALUES (?, ?, ?, ?)`;
		const connection = getDatabase();
		console.log(sql, [data.ArticleID, userId, data.ParentCommentID || null, data.Content])
		await connection.execute(sql, [data.ArticleID, userId, data.ParentCommentID || null, data.Content]);
		// 更新评论数
		const updateSql = `UPDATE articles SET comments = (SELECT COUNT(*) FROM article_comments WHERE article_id = ?) WHERE id = ?`;
		await connection.execute(updateSql, [data.ArticleID, data.ArticleID]);
		return true;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 删除评论
const deleteComment = async (commentId, userId) => {
	try {
		const sql = `DELETE FROM article_comments WHERE id = ? AND user_id = ?`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, [commentId, userId]);
		return rows.affectedRows === 1;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 文章isLike
const queryArticleIsLike = async (articleId, userId) => {
	try {
		const sql = `SELECT * FROM article_likes WHERE article_id = ? AND user_id = ?`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, [articleId, userId]);
		return rows.length > 0;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 文章isCollect
const queryArticleIsCollect = async (articleId, userId) => {
	try {
		const sql = `SELECT * FROM article_favorites WHERE article_id = ? AND user_id = ?`;
		const connection = getDatabase();
		const [rows] = await connection.execute(sql, [articleId, userId]);
		return rows.length > 0;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 文章点赞
const articleLike = async (articleId, userId) => {
	try {
		const isLike = await queryArticleIsLike(articleId, userId);
		const connection = getDatabase();
		if (isLike) {
			const sql = `DELETE FROM article_likes WHERE article_id = ? AND user_id = ?`;
			await connection.execute(sql, [articleId, userId]);
		} else {
			const sql = `INSERT INTO article_likes (article_id, user_id) VALUES (?, ?)`;
			await connection.execute(sql, [articleId, userId]);
		}
		const updateSql = `UPDATE articles SET likes = (SELECT COUNT(*) FROM article_likes WHERE article_id = ?) WHERE id = ?`;
		await connection.execute(updateSql, [articleId, articleId]);
		return true;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 文章收藏
const articleCollect = async (articleId, userId) => {
	try {
		// 还有user_stats表的collections字段需要更新, 这里应该先查询是否已经收藏，如果已经收藏则取消收藏（删除记录），如果没有收藏则收藏（插入记录），同时更新文章表的收藏数,如果没有该用户则插入，有该用户则更新
		const isCollect = await queryArticleIsCollect(articleId, userId);
		const connection = getDatabase();
		if (isCollect) {
			const sql = `DELETE FROM article_favorites WHERE article_id = ? AND user_id = ?`;
			await connection.execute(sql, [articleId, userId]);
		} else {
			const sql = `INSERT INTO article_favorites (article_id, user_id) VALUES (?, ?)`;
			await connection.execute(sql, [articleId, userId]);
		}
		const updateSql = `UPDATE articles SET favorites = (SELECT COUNT(*) FROM article_favorites WHERE article_id = ?) WHERE id = ?`;
		await connection.execute(updateSql, [articleId, articleId]);
		// 更新user_stats表
		const statsSql = `SELECT * FROM user_stats WHERE user_id = ?`;
		const [statsRows] = await connection.execute(statsSql, [userId]);
		if (statsRows.length === 0) {
			const insertSql = `INSERT INTO user_stats (user_id, article_favorites_count) VALUES (?, ?)`;
			await connection.execute(insertSql, [userId, 1]);
		} else {
			const updateStatsSql = `UPDATE user_stats SET article_favorites_count = collections + 1 WHERE user_id = ?`;
			await connection.execute(updateStatsSql, [userId]);
		}
		return true;
	} catch (error) {
		console.error(error);
		return false;
	}
}

// 导出模块
module.exports = {
	// 获取文章列表
	getArticleList,
	// 获取文章详情
	getArticleDetail,
	// 发布文章
	publishArticle,
	// 删除文章
	deleteArticle,
	// 获取必读榜
	getReadList,
	// 获取文章评论
	getArticleComment,
	// 查询是否评论点赞
	queryIsLike,
	// 评论点赞
	commentLike,
	// 发表评论
	publishComment,
	// 删除评论
	deleteComment,
	// 文章isLike
	queryArticleIsLike,
	// 文章isCollect
	queryArticleIsCollect,
	// 文章点赞
	articleLike,
	// 文章收藏
	articleCollect
}
